package com.fare.taluo.action.crawl.excel;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;

import org.apache.commons.lang3.math.NumberUtils;

import com.fare.taluo.io.excel.ExcelImportException;
import com.fare.taluo.io.excel.ImportExcelClassic;
import com.fare.taluo.util.DateUtils;
import com.fare.taluo.util.StringUtils;
import com.fare.taluo.vo.crawl.CrawlLineVO;

public class CrawlLineExcelImport extends ImportExcelClassic {
	/**
	 * 从第几行开始读取数据
	 */
	private int startRow = 5;
	
	private Sheet sheet;
	@Override
	protected Object importData() throws ExcelImportException {
		Cell[] cells = null;
		String no = null;
		String website = null;
		String fromCity = null;
		String toCity = null;
		String startEffectDate = null;
		String endEffectDate = null;
		String frequency = null;
		String hasReturn = null;
		String returnDays = null;
		String priority = null;
		String gradStartDays = null;
		String gradEndDays = null;
		String assDay=null;
		String assFriDay=null;
		List<CrawlLineVO> lines = new ArrayList<CrawlLineVO>();
		CrawlLineVO vo = null;
		StringBuilder sbuilder = new StringBuilder();
		try{
			for (int i = startRow; i < sheet.getRows()-1; i++) {
				cells = sheet.getRow(i);
				no = cells[0].getContents().trim();
				website = cells[1].getContents().trim();
				//只取城市三字码
				fromCity = cells[2].getContents().trim();
				toCity = cells[4].getContents().trim();
				startEffectDate = cells[6].getContents().trim();
				endEffectDate = cells[7].getContents().trim();
				gradStartDays = cells[8].getContents().trim();
				gradEndDays = cells[9].getContents().trim();
				frequency = cells[10].getContents().trim();
				hasReturn = cells[11].getContents().trim();
				assDay = cells[12].getContents().trim();
				assFriDay=cells[13].getContents().trim();
				priority = cells[14].getContents().trim();
				returnDays="3";
				
				//若该行所有列值都为空，则跳过该行
				if(StringUtils.isBlank(no)
						&& StringUtils.isBlank(website)
						 && StringUtils.isBlank(fromCity) && StringUtils.isBlank(toCity) 
						 && StringUtils.isBlank(startEffectDate) && StringUtils.isBlank(endEffectDate) 
						 && StringUtils.isBlank(frequency) && StringUtils.isBlank(hasReturn) 
						 && StringUtils.isBlank(assDay) && StringUtils.isBlank(priority) && StringUtils.isBlank(assFriDay)
						 && StringUtils.isBlank(gradStartDays) && StringUtils.isBlank(gradEndDays))
					continue;
				//验证数据有效性
				sbuilder.append(this.validSheet(i, no,website, fromCity, toCity,
						startEffectDate,endEffectDate, frequency, hasReturn, assDay,assFriDay,priority, gradStartDays,gradEndDays));
				//若是有验证行数据不通过，会返回错误信息提示，则不需要组装对象了
				if(sbuilder.length() <= 0){
					vo = new CrawlLineVO();
					vo.setWebSite(website);
					vo.setFromCity(fromCity);
					vo.setToCity(toCity);
					vo.setStartEffectDate(StringUtils.isNotEmpty(startEffectDate) 
							? DateUtils.strToDate(startEffectDate, "yyyy-MM-dd")
									: new Date());
					vo.setEndEffectDate(StringUtils.isNotEmpty(endEffectDate) 
							? DateUtils.strToDate(endEffectDate, "yyyy-MM-dd")
									: new Date(Integer.MAX_VALUE));
					vo.setGradStartDays(StringUtils.isEmpty(gradStartDays) || !NumberUtils.isDigits(gradStartDays) 
							? 3 : (Integer.valueOf(gradStartDays)>60 || Integer.valueOf(returnDays)<0 ? 0 : Integer.valueOf(gradStartDays)));
					vo.setGradEndDays(StringUtils.isEmpty(gradEndDays) || !NumberUtils.isDigits(gradEndDays) 
							? 3 : (Integer.valueOf(returnDays)>60 || Integer.valueOf(returnDays)<0 ? 60 : Integer.valueOf(gradEndDays)));
					vo.setFrequency(Integer.valueOf(frequency));
					vo.setHasReturn(StringUtils.isNotEmpty(hasReturn) && hasReturn.equals("是") ?
							true : false);
					vo.setAssignDay(assDay);
					vo.setAssignFrightDay(assFriDay);
				/*	vo.setReturnDays(StringUtils.isEmpty(returnDays) || !NumberUtils.isDigits(returnDays) 
							? 3 : (Integer.valueOf(returnDays)>30 || Integer.valueOf(returnDays)<1 ? 0 : Integer.valueOf(returnDays)));*/
					vo.setPriority(StringUtils.isEmpty(priority) || !NumberUtils.isDigits(priority) ? 
							0 : (Integer.valueOf(priority)>1000 || Integer.valueOf(priority)<0 ? 0 : Integer.valueOf(priority)));
					
					lines.add(vo);
				}
			}
			//是验证了所有行数据后，再抛出异常提示
			if(sbuilder.length() > 0)
				throw new ExcelImportException(sbuilder.toString());
		}finally{
			cells = null;
			no = null;
			website = null;
			fromCity = null;
			toCity = null;
			startEffectDate = null;
			endEffectDate = null;
			frequency = null;
			hasReturn = null;
			returnDays = null;
			priority = null;
			vo = null;
			sheet = null;
		}
		return lines;
	}
	private String validSheet(int row, String no,String website, String fromCity,String toCity,
			String startEffectDate,String endEffectDate,
			String frequency,String hasReturn,String assDay,String assFriDay,String priority,String gradStartDays, String gradEndDays) throws ExcelImportException{
		row += 1;
		StringBuilder sbuilder = new StringBuilder();
		if(StringUtils.isEmpty(no.trim())) 
			sbuilder.append(formatNotNull(row, "序号"));
		if(!NumberUtils.isDigits(no.trim()))
			sbuilder.append(formatNotDigits(row, "序号"));
		if(StringUtils.isEmpty(website.trim())) 
			sbuilder.append(formatNotNull(row, "网站名称"));
		if(StringUtils.isEmpty(fromCity.trim())) 
			sbuilder.append(formatNotNull(row, "起点城市三字码"));
		if(StringUtils.isEmpty(toCity.trim())) 
			sbuilder.append(formatNotNull(row, "终点城市三字码"));
		Date  sedate = null;
		if(StringUtils.isNotEmpty(startEffectDate.trim())){
			sedate = DateUtils.strToDate(startEffectDate, "yyyy-MM-dd");
			if(sedate == null)
				sbuilder.append(formatRegex(row, "有效期开始", "yyyy-MM-dd"));
		} 
		Date eedate = null;
		if(StringUtils.isNotEmpty(endEffectDate.trim())){
			eedate = DateUtils.strToDate(endEffectDate, "yyyy-MM-dd");
			if(eedate == null)
				sbuilder.append(formatRegex(row, "有效期结束", "yyyy-MM-dd"));
		} 
		if(sedate != null && eedate != null){
			if(sedate.after(eedate)){
				sbuilder.append(String.format("第[%s]行有效结束日期不可小于开始日期\n",row));
			}
		}
		if(StringUtils.isEmpty(frequency.trim())) 
			sbuilder.append(formatNotNull(row, "抓取频率"));
		if(!NumberUtils.isDigits(frequency.trim()))
			sbuilder.append(formatNotDigits(row, "抓取频率"));
		int startGradDays = 0;
		if(StringUtils.isNotEmpty(gradStartDays.trim())
				&& NumberUtils.isDigits(gradStartDays)){
			startGradDays = Integer.valueOf(gradStartDays);
		}
		int endGradDays = 30;
		if(StringUtils.isNotEmpty(gradEndDays.trim())
				&& NumberUtils.isDigits(gradEndDays)){
			startGradDays = Integer.valueOf(gradEndDays);
		}
		if(endGradDays < startGradDays){
			sbuilder.append(String.format("第[%s]行抓取结束天数不可小于开始天数\n",row));
		}
		return sbuilder.toString();
	}
	
	private String formatNotNull(int row, String colName){
		return String.format("第[%s]行,列[%s]不可为空\n",row, colName);
	}
	private String formatNotDigits(int row, String colName){
		return String.format("第[%s]行,列[%s]非整型数字\n",row, colName);
	}
	private String formatRegex(int row, String colName,String format){
		if(format != null)
			return String.format("第[%s]行,列[%s]格式不正确,应为%s\n",row, colName,format);
		return  String.format("第[%s]行,列[%s]格式不正确",row, colName);
	}
	@Override
	protected boolean validateData() throws ExcelImportException {
		sheet = wb.getSheet(0);
		int cols = sheet.getColumns();
		if(cols < 12){
			throw new ExcelImportException("Excel表格不符合当前需要的格式!");
		}
		int rows = sheet.getRows();
		
		if(rows < 6){
			throw new ExcelImportException("Excel文件中没有需要导入的数据!");
		}
	
		return true;
	}

}
